import requests
from lxml import etree

import pandas as pd
import numpy as np
import time
import pymysql
from sqlalchemy import create_engine
import tushare as ts


# https://blog.csdn.net/S_o_l_o_n/article/details/83933926
def getdata(querydate):
    fh = open('d:/data/whitehorse/btc.csv', 'w', encoding='utf-8')
    title='nowdate,open,high,low,close,volume,market_cap\n'
    fh.write(title)
    if(querydate=='none'):
        fh.close()
        return
    #https://coinmarketcap.com/currencies/bitcoin/historical-data/?start=20200101&end=20201230
    res1 = requests.get(r'https://coinmarketcap.com/currencies/bitcoin/historical-data/?start='+querydate)
    selector = etree.HTML(res1.text)
    oridates = selector.xpath('//td[@class="cmc-table__cell cmc-table__cell--sticky cmc-table__cell--left"]/div/text()')
    dates=[]
    for ori in oridates:
        newdate=changeTimeFormat(ori,'%Y-%m-%d')
        dates.append(newdate)
    values = selector.xpath('//td[@class="cmc-table__cell cmc-table__cell--right"]/div/text()')
    num=0;
    for date in dates:
        fh.write('"'+date+'","'+str(values[num*6+0]).replace(',','')+'","'+str(values[num*6+1]).replace(',','')+'","'+str(values[num*6+2]).replace(',','')+'","'+str(values[num*6+3]).replace(',','')+'","'+str(values[num*6+4]).replace(',','')+'","'+str(values[num*6+5]).replace(',','')+'"\n' )
        num=num+1
    fh.close()
def changeTimeFormat(oritime,foramt):
    tm=time.mktime(time.strptime(oritime,'%b %d, %Y'))
    local_time=time.localtime(tm)
    newtime=time.strftime(foramt,local_time)
    return newtime
def queryMaxDate():
    db = pymysql.Connect('localhost', 'root', 'abcde@124', 'test')
    # 创建游标
    cursor = db.cursor()
    sql = 'select date_add(max(nowdate), interval 1 day)  as startdate  from invest_btc '
    cursor.execute(sql)
    dates = cursor.fetchone()
    db.commit()
    # 关闭游标
    cursor.close()
    maxdate=str(dates[0]).replace('-','')
    today = time.strftime('%Y%m%d', time.localtime())
    if maxdate<today:
        return maxdate
    else:
        return "none"
def getToday():
    today=time.strftime('%Y%m%d', time.localtime())
    print(today)
def insertData():
    df = pd.read_csv('d:/data/whitehorse/btc.csv')
    if len(df)==0:
        print('no date!')
        return
    engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format('root', 'abcde@124', 'localhost:3306', 'test'))
    df.to_sql(name='invest_btc', con=engine, if_exists='append', index=False)
def startup():
    querydate = queryMaxDate()
    getdata(querydate)
    insertData()

#getdata()
#changeTimeFormat('Mar 21, 2020','%Y-%m-%d')

startup()
